﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;

namespace Skyworth.CRM.DAL
{
   public class DepartmentDAL: SuperDataBase,IDepartmentDAL
    {
       public DataTable GetAllDepartment()
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("select * from T_BasicData_Department");
               //strSql.Append("WITH DeptCTE(DepartmentId, DepartmentName, Description, ParentId, Level, tempLevel) AS ");
               //strSql.Append(" (SELECT   DepartmentId, DepartmentName, Description, ParentId, Level,0 as tempLevel ");
               //strSql.Append(" FROM  T_BasicData_Department WHERE ParentId = 0 ");
               //strSql.Append(" UNION ALL SELECT E.DepartmentId,E.DepartmentName,E.Description,E.ParentId,E.Level,M.tempLevel + 1");
               //strSql.Append(" FROM T_BasicData_Department AS E INNER JOIN DeptCTE AS M ON E.ParentId = M.DepartmentId)");
               //strSql.Append(" SELECT   DepartmentId, DepartmentName, Description, ParentId, Level, tempLevel FROM DeptCTE");)
               return DB.ExecuteDataSet(CommandType.Text,strSql.ToString()).Tables[0];
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }

       public DataTable Query(string strWhere)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("select * from T_BasicData_Department where 1=1 ");
               strSql.Append(strWhere);
               return DB.ExecuteDataSet(CommandType.Text, strSql.ToString()).Tables[0];
           }
           catch (Exception ex)
           {               
               throw ex;
           }           
       }

       public DataTable GetAllSubDepartment(int parentId)
       {
           throw new NotImplementedException();
       }

       public DataTable GetSubDepartment(int parentId)
       {
           throw new NotImplementedException();
       }

       public bool Add(Department department)
       {
           Boolean result = false;
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("insert into T_BasicData_Department(");
               strSql.Append("DepartmentName,Description,ParentId,level)");
               strSql.Append(" values (");
               strSql.Append("@Name,@Description,@ParentId,@level)");
         
               DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
               DB.AddInParameter(cmd, "@Name", DbType.String, department.DepartmentName);
               DB.AddInParameter(cmd, "@Description", DbType.String, department.Description);
               DB.AddInParameter(cmd, "@ParentId", DbType.Int16, department.ParentId);
               DB.AddInParameter(cmd, "@level", DbType.Int16, department.Level);
               DB.ExecuteNonQuery(cmd);

               result=true;
           }
           catch (Exception ex)
           {
               throw ex;
               
           }
           return result;

       }

       public bool Update(Department department)
       {
           Boolean result = false;
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("update T_BasicData_Department set ");
               strSql.Append("DepartmentName=@Name,");
               strSql.Append("Description=@Description,");
               strSql.Append("ParentId=@ParentId,");
               strSql.Append("level=@level");
               strSql.Append(" where DepartmentId=@DeptId");

               DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
               DB.AddInParameter(cmd, "@Name", DbType.String, department.DepartmentName);
               DB.AddInParameter(cmd, "@Description", DbType.String, department.Description);
               DB.AddInParameter(cmd, "@ParentId", DbType.Int16, department.ParentId);
               DB.AddInParameter(cmd, "@level", DbType.Int16, department.Level);
               DB.AddInParameter(cmd, "@DeptId", DbType.Int16, department.DepartId);
               
               DB.ExecuteNonQuery(cmd);

               result = true;
           }
           catch (Exception ex)
           {
               throw ex;

           }
           return result;
       }

       public bool Delete(int departmentId)
       {
           Boolean result = false;
           
           StringBuilder strSql = new StringBuilder();
           strSql.Append(
               " WITH Temp_Table AS (select *,code=cast(DepartmentId as varchar(max)) from T_BasicData_Department where DepartmentId in " +
               " (SELECT DepartmentId FROM T_BasicData_Department WHERE DepartmentId =@DeptId) UNION ALL SELECT A.*,code=code+cast(A.DepartmentId as varchar(max)) FROM T_BasicData_Department A " +
               " INNER JOIN Temp_Table B ON A.ParentId = B.DepartmentId ) select * into #temp from Temp_Table " +
               " if not exists(select 1 from  T_SYS_Users  A left join T_BasicData_Employee B on A.IndividualId=B.EmpId where B.DeptID in (select DepartmentId from #temp) and A.UserType=1)" +
               " begin delete T_BasicData_Department where DepartmentId in (select DepartmentId from #temp) end" +
               " else begin Raiserror('该部门或其子部门下还存在用户信息，该部门不能被删除！',16,1) end");
           
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@DeptId", DbType.Int32, departmentId);
           DB.ExecuteNonQuery(cmd);

           return true;
       }
    }
}
